package br.com.medmark.model.hibernate.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import br.com.medmark.model.hibernate.AreaAtuacao;
import br.com.medmark.model.hibernate.Estado;
import br.com.medmark.model.hibernate.Profissao;
import br.com.medmark.model.hibernate.Profissional;

public class UtilDAOImpl  {
	
private static UtilDAOImpl utilDAOImpl = null;

protected EntityManager entityManager = Persistence.createEntityManagerFactory("med_mark").createEntityManager();
	
	private UtilDAOImpl() {
		
		
	}
		
		
	public static UtilDAOImpl getInstance(){
		
		if ( utilDAOImpl == null) {
			utilDAOImpl = new UtilDAOImpl();
		}
	
		return utilDAOImpl;
		
	}
	
	public List<Estado> getEstados() {
		String sql = "FROM Estado";
		
		TypedQuery<Estado> query = this.entityManager.createQuery(sql, Estado.class);

		
		try {
			return query.getResultList();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	public List<Profissao> getProfissoes() {
		String sql = "FROM Profissao";
		
		TypedQuery<Profissao> query = this.entityManager.createQuery(sql, Profissao.class);

		
		try {
			return query.getResultList();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	public List<AreaAtuacao> getAreasAtuacao() {
		String sql = "FROM AreaAtuacao";
		
		TypedQuery<AreaAtuacao> query = this.entityManager.createQuery(sql, AreaAtuacao.class);

		
		try {
			return query.getResultList();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	public List<AreaAtuacao> getAreasAtuacao(int codigoProfissao) {
		String sql = "FROM AreaAtuacao a where a.codigoProfissao = :codigoProfissao";
		
		TypedQuery<AreaAtuacao> query = this.entityManager.createQuery(sql, AreaAtuacao.class);
		query.setParameter("codigoProfissao", codigoProfissao);
		
		try {
			return query.getResultList();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	public AreaAtuacao getAreaAtuacao(int codigo) {
		String sql = "FROM AreaAtuacao a where a.codigo = :codigo";
		
		TypedQuery<AreaAtuacao> query = this.entityManager.createQuery(sql, AreaAtuacao.class);
		query.setParameter("codigo", codigo);
		
		try {
			return query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	
	public List<AreaAtuacao> getAreasAtuacao(String profissao) {
		String sql2 = "FROM Profissao p where p.descricao = :profissao";
		TypedQuery<Profissao> query2 = this.entityManager.createQuery(sql2, Profissao.class);
		query2.setParameter("profissao", profissao);
		Profissao prof = query2.getSingleResult();
		int codigoProfissao = prof.getCodigo();
		String sql = "FROM AreaAtuacao a where a.codigoProfissao = :codigoProfissao";
		
		/*TypedQuery<AreaAtuacao> query = this.entityManager.createQuery(sql);*/
		TypedQuery<AreaAtuacao> query = this.entityManager.createQuery(sql,AreaAtuacao.class);
		query.setParameter("codigoProfissao", codigoProfissao);
		
		try {
			return  query.getResultList();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	public Profissao getProfissao(int codigo) {
		String sql = "FROM Profissao p where p.codigo = :codigo";
		
		TypedQuery<Profissao> query = this.entityManager.createQuery(sql, Profissao.class);
		query.setParameter("codigo", codigo);
		
		try {
			return query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		}
	}
	
	

}
